import { connection } from '../app/database/mysql'
import { PostModel } from './post.model'
import { sqlFragment } from './post.provider'

export interface GetPostsOptionsFilter {
  name: string
  sql?: string
  param?: string
}

export interface GetPostsOptionsPagination {
  limit: number
  offset: number
}

interface GetPostsOptions {
  sort?: string
  filter?: GetPostsOptionsFilter
  pagination?: GetPostsOptionsPagination
}

export const getPosts = async ({
  sort,
  filter,
  pagination: { limit, offset }
}: GetPostsOptions) => {
  let params: Array<any> = [limit, offset]

  if (filter.param) {
    params = [filter.param, ...params]
  }

  const statement = `
    SELECT 
      post.id, 
      post.title, 
      post.content, 
      ${sqlFragment.user},
      ${sqlFragment.totalComments},
      ${sqlFragment.file},
      ${sqlFragment.tags},
      ${sqlFragment.totalLikes}
    FROM post 
      ${sqlFragment.leftJoinUser}
      ${sqlFragment.leftJoinOneFile}
      ${sqlFragment.leftJoinTag}
      ${filter.name == 'userLiked' ? sqlFragment.innerJoinUserLikePost : ''}
    WHERE ${filter.sql}
    GROUP BY post.id
    ORDER BY ${sort}
    LIMIT ?
    OFFSET ?
  `

  const [rows] = await connection.promise().query(statement, params)
  return rows
}

export const createPost = async (post: PostModel) => {
  const statement = `INSERT INTO post SET ?`
  const [data] = await connection.promise().query(statement, post)
  return data
}

export const updatePost = async (postId: number, post: PostModel) => {
  const statement = `UPDATE POST SET ? WHERE id = ?`
  const [data] = await connection.promise().query(statement, [post, postId])
  return data
}

export const deletePost = async (postId: number) => {
  const statement = `DELETE FROM post WHERE id = ?`
  const [data] = await connection.promise().query(statement, postId)
  return data
}

export const createPostTag = async (postId: number, tagId: number) => {
  const statement = `INSERT INTO post_tag (post_id, tag_id) VALUES (?, ?)`
  const [data] = await connection.promise().query(statement, [postId, tagId])
  return data
}

export const postHasTag = async (postId: number, tagId: number) => {
  const statement = `SELECT * FROM post_tag WHERE post_id = ? AND tag_id = ?`
  const [data] = await connection.promise().query(statement, [postId, tagId])
  return !!data[0]
}

export const deletePostTag = async (postId: number, tagId: number) => {
  const statement = `DELETE FROM post_tag WHERE post_id = ? AND tag_id = ?`
  const [data] = await connection.promise().query(statement, [postId, tagId])
  return data
}

export const getPostsTotalCount = async ({ filter }: GetPostsOptions) => {
  let params = [filter.param]

  const statement = `SELECT COUNT(DISTINCT post.id) as total FROM post ${
    sqlFragment.leftJoinUser
  } ${sqlFragment.leftJoinOneFile} ${sqlFragment.leftJoinTag} ${
    filter.name == 'userLiked' ? sqlFragment.innerJoinUserLikePost : ''
  } WHERE ${filter.sql}`

  const [data] = await connection.promise().query(statement, params)

  return data[0].total
}

export const getPostById = async (postId: number) => {
  const statement = `
    SELECT 
      post.id, 
      post.title, 
      post.content, 
      ${sqlFragment.user},
      ${sqlFragment.totalComments},
      ${sqlFragment.file},
      ${sqlFragment.tags},
      ${sqlFragment.totalLikes}
    FROM post 
      ${sqlFragment.leftJoinUser}
      ${sqlFragment.leftJoinOneFile}
      ${sqlFragment.leftJoinTag}
    WHERE post.id = ?
  `

  const [data] = await connection.promise().query(statement, postId)
  if (!data[0].id) throw new Error('NOT_FOUND')

  return data[0]
}
